﻿using Microsoft.SqlServer.Dts;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Reflection;
using Microsoft.SharePoint.Client;
using System.Security;

namespace SharepointOnlineDestination
{
    [DtsPipelineComponent(DisplayName = "SharepointOnline Destination", ComponentType = ComponentType.DestinationAdapter, IconResource = "SharepointOnlineDestination.Destination16.ico")]
    public class SharepointOnlineDestination : PipelineComponent
    {
        int[] cols;
        string[] colname;
        ClientContext cont;
        Web web;
        string temp_server, temp_user, temp_password, temp_listname;

        public void getconnection(string server, string username, string password)
        {

            cont = new ClientContext(server);
            SecureString Password = new SecureString();
            foreach (char c in password.ToCharArray())
            {
                Password.AppendChar(c);
            }
            cont.Credentials = new SharePointOnlineCredentials(username, Password);
        }

        public List<Field> getaccessiblecolumns(string server, string username, string password, string listname)
        {
            getconnection(server, username, password);
            Web web = cont.Web;
            cont.Load(web);
            cont.ExecuteQuery();

            sstest = web.Lists.GetByTitle(listname);
            var fs = sstest.Fields;

            //string operatyion_type = ComponentMetaData.CustomPropertyCollection[4].Value;
            /*if (operatyion_type == "Update")
            {*/
                cont.Load(fs, fields => fields.Where(field => !(field.Hidden || field.ReadOnlyField || field.Group == "_Hidden" || field.FieldTypeKind == FieldType.Attachments) || field.InternalName == "ID"));
            /*}
            else
            {*/
              //  cont.Load(fs, fields => fields.Where(field => !(field.Hidden || field.ReadOnlyField || field.Group == "_Hidden" || field.FieldTypeKind == FieldType.Attachments)));
            //}
            sstest.GetRelatedFields();
            cont.ExecuteQuery();
            return fs.ToList<Field>();
        }

        public List getlist(string server, string username, string password, string listname)
        {
            getconnection(server, username, password);
            Web web = cont.Web;
            cont.Load(web);
            cont.ExecuteQuery();

            sstest = web.Lists.GetByTitle(listname);
            var fs = sstest.Fields;
            //string operatyion_type = ComponentMetaData.CustomPropertyCollection[4].Value;
            /*if (operatyion_type == "Update")
            {*/
                cont.Load(fs, fields => fields.Where(field => !(field.Hidden || field.ReadOnlyField || field.Group == "_Hidden" || field.FieldTypeKind == FieldType.Attachments) || field.InternalName == "ID"));
           /* }
            else
            {*/
               // cont.Load(fs, fields => fields.Where(field => !(field.Hidden || field.ReadOnlyField || field.Group == "_Hidden" || field.FieldTypeKind == FieldType.Attachments)));
            //}
            sstest.GetRelatedFields();
            cont.ExecuteQuery();
            return sstest;
        }

        public override void ProvideComponentProperties()
        {

            base.RemoveAllInputsOutputsAndCustomProperties();
            ComponentMetaData.RuntimeConnectionCollection.RemoveAll();
            IDTSInput100 input = ComponentMetaData.InputCollection.New();
            input.Name = "Input";
            input.ExternalMetadataColumnCollection.IsUsed = true;


            ComponentMetaData.Name = "SharepointOnlineDestination";
            ComponentMetaData.Description = "Destination Component to push data in sharepoint online List";
            ComponentMetaData.ContactInfo = "dilkushp@hotmail.com";

            IDTSCustomProperty100 SharepointServer = ComponentMetaData.CustomPropertyCollection.New();
            SharepointServer.Name = "Server";
            SharepointServer.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
            SharepointServer.Description = "name of sharepoimnt server <mysite>.sharepoint.com";
            SharepointServer.Value = "https://<mysite>@sharepoint.com";

            IDTSCustomProperty100 User_Name = ComponentMetaData.CustomPropertyCollection.New();
            User_Name.Name = "User";
            User_Name.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
            User_Name.Description = "USER nAME FOR sHAREPOINT ONLINE AUTHENTICATION";
            User_Name.Value = "<user>@<my site>.microsoft.com";

            IDTSCustomProperty100 Password = ComponentMetaData.CustomPropertyCollection.New();
            Password.Name = "Password";
            Password.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
            Password.EncryptionRequired = true;
            Password.Description = "Password for sharepoint online user";
            Password.Value = "<Password>";

            IDTSCustomProperty100 ListName = ComponentMetaData.CustomPropertyCollection.New();
            ListName.Name = "List name";
            ListName.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
            ListName.Description = "Name of HSraepoint list we need to fetch";
            ListName.Value = "<List>";
            
            IDTSCustomProperty100 OperationType = ComponentMetaData.CustomPropertyCollection.New();
            OperationType.Name = "OperationType";
            OperationType.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
            OperationType.Description = "Insert-Update || Delete";
            OperationType.Value = "Insert-Update || Delete";
        }

        public override DTSValidationStatus Validate()
        {
            var status = DTSValidationStatus.VS_ISVALID;
            string server = ComponentMetaData.CustomPropertyCollection[0].Value;
            string user = ComponentMetaData.CustomPropertyCollection[1].Value;
            string password = ComponentMetaData.CustomPropertyCollection[2].Value;
            string listname = ComponentMetaData.CustomPropertyCollection[3].Value;
            //string operatyion_type = ComponentMetaData.CustomPropertyCollection[4].Value;


            string[] server_parts = server.Split('.');
            string[] user_parts = user.Split('@');

            if ((server_parts.Length < 3) || (server_parts[0].Equals("")) || (server_parts[1].Equals("")) || (server_parts[2].Equals("")))// || (!server_parts[1].ToLower().Equals("sharepoint")) || (!server_parts[2].ToLower().Contains("com")))
            {
                status = DTSValidationStatus.VS_ISBROKEN;
            }
            else if ((user_parts.Length != 2) || (user_parts[0].Equals("")) )
            {
                status = DTSValidationStatus.VS_ISBROKEN;
            }
            else if (password.Equals(""))
            {
                status = DTSValidationStatus.VS_ISBROKEN;
            }
            else if (listname.Equals(""))
            {
                status = DTSValidationStatus.VS_ISBROKEN;
            }
            /*else if (!(operatyion_type == "Update" || operatyion_type == "Insert"))
            {
                status = DTSValidationStatus.VS_ISBROKEN;
            }*/
            else if ((server != temp_server || user != temp_user || password != temp_password || listname != temp_listname) && (server != "https://<mysite>@sharepoint.com" && listname != "<List>" && user == "<user>@<my site>.microsoft.com" && password == "<Password>"))
            {
                temp_listname = listname;
                temp_password = password;
                temp_server = server;
                temp_user = user;
                //status = DTSValidationStatus.VS_NEEDSNEWMETADATA;
                this.ReinitializeMetaData();
            }

            return status;
        }

        public override void OnInputPathAttached(int inputID)
        {
            IDTSInput100 objectByID = base.ComponentMetaData.InputCollection.GetObjectByID(inputID);
            IDTSVirtualInput100 virtualInput = objectByID.GetVirtualInput();
            foreach (IDTSVirtualInputColumn100 virtualInputColumnCollection in virtualInput.VirtualInputColumnCollection)
            {
                this.SetUsageType(inputID, virtualInput, virtualInputColumnCollection.LineageID, DTSUsageType.UT_READONLY);
            }
            objectByID.ExternalMetadataColumnCollection.RemoveAll();
            if (this._existingColumnData == null)
            {
                this._existingColumnData = new Dictionary<string, string>();
            }
            this.LoadDataSourceInformation(this._existingColumnData);
        }

        public override void PreExecute()
        {
            base.PreExecute();
            IEnumerable<IDTSInputColumn100> collection = base.ComponentMetaData.InputCollection[0].InputColumnCollection.Cast<IDTSInputColumn100>();
            IEnumerable<IDTSExternalMetadataColumn100> collection1 = base.ComponentMetaData.InputCollection[0].ExternalMetadataColumnCollection.Cast<IDTSExternalMetadataColumn100>();
            Func<IDTSInputColumn100, int> externalMetadataColumnID = (IDTSInputColumn100 col) => col.ExternalMetadataColumnID;
            var collection2 = collection.Join(collection1, externalMetadataColumnID, (IDTSExternalMetadataColumn100 metaCol) => metaCol.ID, (IDTSInputColumn100 col, IDTSExternalMetadataColumn100 metaCol) => new { Name = (string)((object)metaCol.CustomPropertyCollection["Id"].Value), BufferColumn = base.BufferManager.FindColumnByLineageID(base.ComponentMetaData.InputCollection[0].Buffer, col.LineageID) });
            this._bufferLookup = collection2.ToDictionary((a) => a.Name, (a) => a.BufferColumn);
            IEnumerable<IDTSInputColumn100> collection3 = base.ComponentMetaData.InputCollection[0].InputColumnCollection.Cast<IDTSInputColumn100>();
            IEnumerable<IDTSExternalMetadataColumn100> collection4 = base.ComponentMetaData.InputCollection[0].ExternalMetadataColumnCollection.Cast<IDTSExternalMetadataColumn100>();
            Func<IDTSInputColumn100, int> func = (IDTSInputColumn100 col) => col.ExternalMetadataColumnID;
            Func<IDTSExternalMetadataColumn100, int> d = (IDTSExternalMetadataColumn100 metaCol) => metaCol.ID;
            var collection5 = collection3.Join(collection4, func, d, (IDTSInputColumn100 col, IDTSExternalMetadataColumn100 metaCol) => new { Name = (string)((object)metaCol.CustomPropertyCollection["Id"].Value), DataType = col.DataType });
            this._bufferLookupDataType = collection5.ToDictionary((a) => a.Name, (a) => a.DataType);
        }

        public override void ReinitializeMetaData()
        {
            if (base.ComponentMetaData.InputCollection.Count > 0)
            {
                IDTSInput100 item = base.ComponentMetaData.InputCollection[0];
                IEnumerable<IDTSExternalMetadataColumn100> collection = item.ExternalMetadataColumnCollection.Cast<IDTSExternalMetadataColumn100>();
                var collection1 = collection.Select((IDTSExternalMetadataColumn100 metaCol) =>
                {
                    string name = metaCol.Name;
                    return new { ColumnName = name, SpColName = (string)((object)metaCol.CustomPropertyCollection["Id"].Value) };
                });
                //var spColName = (a) => a.SpColName;
                this._existingColumnData = collection1.ToDictionary((a) => a.SpColName, (a) => a.ColumnName);
                //base.ComponentMetaData.InputCollection[0].InputColumnCollection.RemoveAll();
                this.OnInputPathAttached(base.ComponentMetaData.InputCollection[0].ID);
            }
            base.ReinitializeMetaData();
        }

        public override void ProcessInput(int inputID, PipelineBuffer buffer)
        {

            getconnection(ComponentMetaData.CustomPropertyCollection[0].Value, ComponentMetaData.CustomPropertyCollection[1].Value, ComponentMetaData.CustomPropertyCollection[2].Value);

            List sst = getlist(ComponentMetaData.CustomPropertyCollection[0].Value, ComponentMetaData.CustomPropertyCollection[1].Value, ComponentMetaData.CustomPropertyCollection[2].Value, ComponentMetaData.CustomPropertyCollection[3].Value);
            string operatyion_type = ComponentMetaData.CustomPropertyCollection[4].Value;
            CamlQuery query = CamlQuery.CreateAllItemsQuery();
            ListItemCollection itemcoll = null;
                itemcoll = sst.GetItems(query);
                cont.Load(itemcoll);
                cont.ExecuteQuery();
            while (buffer.NextRow())
            {
                    ListItemCreationInformation newItem = new ListItemCreationInformation();
                    ListItem listItem = sst.AddItem(newItem);
                    var inp = ComponentMetaData.InputCollection[0].InputColumnCollection.Count;
                    IOrderedEnumerable<IDTSInputColumn100> inpcoll = ComponentMetaData.InputCollection[0].InputColumnCollection.Cast<IDTSInputColumn100>().OrderBy(column => column.LineageID);
                    IOrderedEnumerable<IDTSExternalMetadataColumn100> exinpcoll = ComponentMetaData.InputCollection[0].ExternalMetadataColumnCollection.Cast<IDTSExternalMetadataColumn100>().OrderBy(column => column.ID);
                    List<IDTSInputColumn100> inputs = inpcoll.ToList<IDTSInputColumn100>();
                    List<IDTSExternalMetadataColumn100> exinputs = exinpcoll.ToList<IDTSExternalMetadataColumn100>();
                    int tempid = 0;
                    User u=null;
                    FieldUrlValue urls;
                    ListItem tempitem = null;
                        for (int col = 0; col < inp; col++)
                        {
                            if ((!buffer.IsNull(col)) && inputs[col].ExternalMetadataColumnID != 0)
                            {

                                if (buffer[col] != null)
                                {
                                    if (exinputs.Where(column => column.ID == inputs[col].ExternalMetadataColumnID).Single().Description.ToUpper() == "ID")
                                    {
                                        int tmp = 0;
                                        int.TryParse(buffer[col].ToString(), out tmp);
                                        tempid = tmp;
                                        
                                    }
                                    else if (operatyion_type.ToUpper() == "DELETE")
                                    {
                                    }
                                    /*else if (sst.Fields.Where(field => field.InternalName == exinputs.Where(column => column.ID == inputs[col].ExternalMetadataColumnID).Single().Description).Single().TypeAsString == "UserMulti")
                                    {
                                        string[] vals = buffer[col].ToString().Split('|');
                                        
                                        //bjectPathConstructor opc = new ObjectPathConstructor(sst.Fields.Where(field => field.InternalName == exinputs.Where(column => column.ID == inputs[col].ExternalMetadataColumnID).Single().Description).Single().Context, (string)sst.Fields.Where(field => field.InternalName == exinputs.Where(column => column.ID == inputs[col].ExternalMetadataColumnID).Single().Description).Single().GetType().GUID,null);
                                        UserCollection uservalue = new UserCollection(cont, (ObjectPath)listItem[exinputs.Where(column => column.ID == inputs[col].ExternalMetadataColumnID).Single().Description]);
                                        int i = 0;
                                        foreach (string users in vals)
                                        {
                                            uservalue.AddUser(cont.Web.EnsureUser(users.Split(':')[0]));
                                        }
                                        listItem[exinputs.Where(column => column.ID == inputs[col].ExternalMetadataColumnID).Single().Description] = uservalue;
                                    }*/
                                    else if (sst.Fields.Where(field => field.InternalName == exinputs.Where(column => column.ID == inputs[col].ExternalMetadataColumnID).Single().Description).Single().TypeAsString == "User" || sst.Fields.Where(field => field.InternalName == exinputs.Where(column => column.ID == inputs[col].ExternalMetadataColumnID).Single().Description).Single().TypeAsString == "UserMulti")
                                    {
                                        string[] vals = buffer[col].ToString().Split(':');
                                        u = cont.Web.EnsureUser(vals[0]);
                                        listItem[exinputs.Where(column => column.ID == inputs[col].ExternalMetadataColumnID).Single().Description] = u;
                                    }
                                    else if (sst.Fields.Where(field => field.InternalName == exinputs.Where(column => column.ID == inputs[col].ExternalMetadataColumnID).Single().Description).Single().TypeAsString.ToUpper() == "URL")
                                    {
                                        string[] vals = buffer[col].ToString().Split(new string[] { ":D:" }, StringSplitOptions.RemoveEmptyEntries);
                                        urls = new FieldUrlValue();
                                        urls.Url = vals[0];
                                        urls.Description = vals[1];
                                        listItem[exinputs.Where(column => column.ID == inputs[col].ExternalMetadataColumnID).Single().Description] = urls;
                                    }
                                    else
                                    {
                                        listItem[exinputs.Where(column => column.ID == inputs[col].ExternalMetadataColumnID).Single().Description] = buffer[col];
                                    }
                                }
                            }

                        }

                        
                        try
                        {
                            if (tempid != 0)
                            {
                                tempitem=itemcoll.Where(x => (int)x.FieldValues["ID"] == tempid).Single();
                                tempitem = itemcoll.GetById(tempid);
                                cont.ExecuteQuery();
                            }
                        }
                        catch (Exception ex)
                        {
                        }
                    if (tempitem == null)
                    {
                        listItem.Update();
                        cont.ExecuteQuery();
                    }
                    else
                    {
                        if (operatyion_type.ToUpper() == "DELETE")
                        {
                            tempitem.DeleteObject();
                        }
                        else
                        {
                            foreach (var x in listItem.FieldValues)
                            {
                                if ((x.Key != "ID"))
                                {
                                    if (sst.Fields.Where(field => field.InternalName == x.Key).Single().TypeAsString == "User")
                                    {
                                        tempitem[x.Key] = listItem[x.Key];
                                    }
                                    else
                                    {
                                        try
                                        {
                                            tempitem[x.Key] = listItem[x.Key];
                                        }
                                        catch (Exception ex)
                                        {
                                            tempitem[x.Key] = listItem[x.Key];
                                        }
                                    }

                                }

                            }
                            tempitem.Update();
                        }
                        cont.ExecuteQuery();
                    }

            }


        }


        public override void PostExecute()
        {

            base.PostExecute();
        }

        private void LoadDataSourceInformation(Dictionary<string, string> existingColumnData)
        {
            string value = base.ComponentMetaData.CustomPropertyCollection[0].Value;
            string username = base.ComponentMetaData.CustomPropertyCollection[1].Value;
            string password = base.ComponentMetaData.CustomPropertyCollection[2].Value;
            string obj = base.ComponentMetaData.CustomPropertyCollection[3].Value;
            if (base.ComponentMetaData.InputCollection.Count == 1)
            {
                IDTSInput100 item = base.ComponentMetaData.InputCollection[0];
                if (value != null && obj != null)
                {
                    IDTSInput100 variable = item;
                    string str = (string)((dynamic)base.ComponentMetaData.CustomPropertyCollection[0].Value);
                    string value1 = (string)((dynamic)base.ComponentMetaData.CustomPropertyCollection[3].Value);
                    this.CreateExternalMetaDataColumns(variable, value, username, password, obj, existingColumnData);
                }
            }
        }

        private void CreateExternalMetaDataColumns(IDTSInput100 input, string sharepointUrl, string username, string password, string listName, Dictionary<string, string> existingField)
        {
            if (input.Description != listName)
            {
                existingField.Clear();
                input.Description = listName;
            }
            try
            {
                List<Field> accessibleSharePointColumns = this.getaccessiblecolumns(sharepointUrl, username, password, listName);
                List<Field> columnDatas = accessibleSharePointColumns;
                IEnumerable<IGrouping<string, Field>> groupings = columnDatas.GroupBy<Field, string>((Field n) => n.InternalName);
                IEnumerable<IGrouping<string, Field>> groupings1 = groupings.Where<IGrouping<string, Field>>((IGrouping<string, Field> g) => g.Count<Field>() > 1);
                IEnumerable<string> strs = groupings1.Select<IGrouping<string, Field>, string>((IGrouping<string, Field> g) => g.Key);

                foreach (Field accessibleSharePointColumn in accessibleSharePointColumns)
                {
                    IDTSExternalMetadataColumn100 item = input.ExternalMetadataColumnCollection.New();

                    if (existingField.ContainsKey(accessibleSharePointColumn.InternalName))
                    {
                        item.Name = existingField[accessibleSharePointColumn.InternalName];
                    }
                    else if (!strs.Contains<string>(accessibleSharePointColumn.InternalName))
                    {
                        item.Name = accessibleSharePointColumn.Title;
                    }
                    else
                    {
                        item.Name = string.Concat(accessibleSharePointColumn.InternalName, " (", accessibleSharePointColumn.InternalName, ")");
                    }
                    item.Description = accessibleSharePointColumn.InternalName;
                    item.Length = 0;
                    item.Precision = 0;
                    item.Scale = 0;
                    if ("Boolean|AllDayEvent|Attachments|CrossProjectLink|Recurrence".Contains(accessibleSharePointColumn.FieldTypeKind.ToString()))
                    {
                        item.DataType = DataType.DT_BOOL;
                    }
                    else if (accessibleSharePointColumn.FieldTypeKind.ToString() == "DateTime")
                    {
                        item.DataType = DataType.DT_DBTIMESTAMP;
                    }
                    else if ("Number|Currency".Contains(accessibleSharePointColumn.FieldTypeKind.ToString()))
                    {
                        item.DataType = DataType.DT_R8;
                    }
                    else if ("Counter|Integer".Contains(accessibleSharePointColumn.FieldTypeKind.ToString()))
                    {
                        item.DataType = DataType.DT_I4;
                    }

                    else if ("Guid".Contains(accessibleSharePointColumn.FieldTypeKind.ToString()))
                    {
                        item.DataType = DataType.DT_GUID;
                    }
                    else
                    {
                        item.DataType = DataType.DT_WSTR;
                        item.Length = 4000;
                    }
                    IDTSCustomProperty100 name = item.CustomPropertyCollection.New();
                    name.Name = "Id";
                    name.Description = "SharePoint ID";
                    name.Value = accessibleSharePointColumn.Title;
                    //IDTSInputColumn100 d = (IDTSInputColumn100)input.InputColumnCollection.Cast<IDTSInputColumn100>().Where<IDTSInputColumn100>((IDTSInputColumn100 col) => col.Name == item.Name);//.SingleOrDefault<IDTSInputColumn100>();
                    IDTSInputColumn100 d;
                    try
                    {
                       d = input.InputColumnCollection.Cast<IDTSInputColumn100>().Where<IDTSInputColumn100>((IDTSInputColumn100 col) => (col.Name == item.Name) && (col.IsAssociatedWithOutputColumn == true)).First();//.SingleOrDefault<IDTSInputColumn100>();
                    }
                    catch (Exception ex)
                    {
                        d = (IDTSInputColumn100)input.InputColumnCollection.Cast<IDTSInputColumn100>().Where<IDTSInputColumn100>((IDTSInputColumn100 col) => col.Name == item.Name).SingleOrDefault<IDTSInputColumn100>();
                    }
                        if (d == null)
                    {
                        continue;
                    }
                    d.ExternalMetadataColumnID = item.ID;
                }
            }
            catch (Exception ex)
            {
                input.ExternalMetadataColumnCollection.RemoveAll();
                throw;
            }
        }

        public List sstest { get; set; }
        public Dictionary<string, string> _existingColumnData { get; set; }

        public Dictionary<string, int> _bufferLookup { get; set; }
        public Dictionary<string, DataType> _bufferLookupDataType { get; set; }
    }
}
